Mega Code Archive
Date Timestamp Functions
1) Add Negative time
2) ADD_MONTHS(x, y) gets the result of adding y months to x
3) Adding One Month
4) Adding Two Months to SYSDATE
5) An alias is required when using TO_CHAR to pretty up the output
6) Birthday before 1940
7) Calculating the Number of Days of Summer in June
8) Catch exception from to_date function
9) Combine ADD_MONTHS() function with To_Date
10) Combine ADD_MONTHS() with TO_CHAR() and TO_DATE
11) Combine LAST_DAY and ADD_MONTHS together
12) Combining TO_CHAR to Format SYSDATE
13) Converting a Datetime from One Time Zone to Another with NEW_TIME() function
14) CURRENT_TIMESTAMP()
15) Date Functions
16) Datetime Formatting Parameters for ROUND(x[, unit])
17) DBMS_OUTPUT PUT_LINE(TO_DATE (11))
18) DBMS_OUTPUT PUT_LINE(TO_DATE (111))
19) DBMS_OUTPUT PUT_LINE(TO_DATE (12-APR-09))
20) DBMS_OUTPUT PUT_LINE(TO_DATE (19991205))
21) DBMS_OUTPUT PUT_LINE(TO_DATE (61996))
22) Default Output of SYSDATE
23) Differences Between Dates
24) Examples of converting time zones to character strings
25) Examples of the TRUNC function being applied to datetime values
26) Extract day from birthday
27) Extract month from birthday
28) Extract year from birthday
29) Extract year, month, day from a date
30) EXTRACT() gets the hour, minute, and second from a TIMESTAMP returned by TO_TIMESTAMP()
31) EXTRACT(MINUTE FROM TO_TIMESTAMP(01-JAN-2005 19
32) EXTRACT(SECOND FROM TO_TIMESTAMP(01-JAN-2005 19
33) EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ(01-JAN-2005 19
34) EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ(01-JAN-2005 19
35) EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ(01-JAN-2005 19
36) Finding the First Monday After the Current Date and Time
37) Finding the First Monday in the Month of August
38) Finding the First Monday in the Month of September
39) Finding the Last Day of the Month Starting Summer
40) Formatting Dates with TO_CHAR
41) Get the database time zone using the DBTIMEZONE() function
42) Get your session time zone using the SESSIONTIMEZONE() function
43) Getting the Current Date in the Session Time Zone
44) How Oracle Interprets Two-Digit Years
45) Insert a row using the CURRENT_TIMESTAMP function for both columns
46) LAST_DAY(column value)+1
47) Last_day(sysdate)
48) LAST_DAY, TO_CHAR, TO_DATE
49) LOCALTIMESTAMP()
50) Months between birthday and now
51) Months_between current time and birthday
52) MONTHS_BETWEEN(x, y)
53) NEXT_DAY function
54) NEXT_DAY(start_date-1,Tuesday)
55) Next_day(sysdate,SAT)
56) NEXT_DAY(x, day)
57) NUMTODSINTERVAL()
58) NUMTODSINTERVAL(1 5, DAY)
59) NUMTODSINTERVAL(10 123456789, SECOND)
60) NUMTODSINTERVAL(3 25, HOUR)
61) NUMTODSINTERVAL(5, MINUTE)
62) NUMTOYMINTERVAL(1 5, YEAR)
63) NUMTOYMINTERVAL(3 25, MONTH)
64) Obtaining Time Zone Offsets using the TZ_OFFSET() function
65) Order date value by only year field with extract() function
66) Removing the Time from SYSDATE
67) Returned result is a negative number of months
68) Round date to day
69) Round result from months_between
70) ROUND(date_in_1,CC)
71) ROUND(x[, unit])
72) Rounding to the Nearest Minute
73) Rounds May 25, 2005, to the first day in the nearest month
74) Second Tuesday of the Month
75) SELECT ADD_MONTHS(01-JAN-2005, 3) FROM dual
76) SELECT ADD_MONTHS(01-JAN-2005, -3) FROM dual; (2)
77) SELECT ADD_MONTHS(28-NOV-05, 1) FROM DUAL
78) SELECT ADD_MONTHS(29-NOV-05, 1) FROM DUAL
79) SELECT ADD_MONTHS(30-NOV-05, 1) FROM DUAL
80) SELECT ADD_MONTHS(31-DEC-05, -1) FROM DUAL
81) Select date 1954-08-11 + 10000 as 10,000 days
82) Select date 1996-01-29 + interval 1 month as col_1
83) Select date 1997-08-11 - interval 3 month as col_3
84) SELECT EXTRACT(MONTH FROM TO_DATE(01-JAN-2005 19
85) SELECT EXTRACT(YEAR FROM TO_DATE(01-JAN-2005 19
86) SELECT LAST_DAY(01-JAN-2005) FROM dual
87) SELECT MONTHS_BETWEEN(15-JAN-2005, 25-MAY-2005)
88) Select TO_CHAR(sysdate,HH MI SS AM) from dual
89) SELECT TO_TIMESTAMP_TZ(2005-05-13 07
90) Specifying a Datetime Format for TO_DATE() function
91) Specifying Times
92) Subtract 13 months from the January 1, 2005
93) Subtract one date from another, yielding the number of days between the two dates
94) Subtracts two days from August 2, 2003
95) SYSDATE() returns the current datetime set in the operating system
96) SYSTIMESTAMP()
97) The LAST_DAY function returns the last day of any month
98) The NEW_TIME Function
99) The Proper Method to Find the First Monday in a Given Month
100) The ROUND Function
101) Time Change from Chicago to Los Angeles
102) Time Interval Related Functions
103) Timestamp-Related Functions
104) To_char(gather_date, mmddyyyy hh
105) TO_CHAR(Start_Date, Day fmMonth dd, yyyy)
106) TO_CHAR(Start_Date, Day Mon yyyy)
107) TO_CHAR(Start_Date, dd Mon)
108) TO_CHAR(Start_Date, dd month YY)
109) TO_CHAR(Start_Date, dd Month yyyy)
110) TO_CHAR(Start_Date, dd RM yyyy)
111) TO_CHAR(Start_Date, ddMon yy hh24
112) TO_CHAR(Start_Date, Mon ddsp yyyy)
113) TO_CHAR(sysdate,HH24
114) TO_CHAR(TO_DATE(04-JUL-15, DD-MON-RR), DD-MON-YYYY)
115) TO_DATE function to convert from characters to dates explicitly
116) TO_DATE() converts the strings 04-JUL-2006 to the date July 4, 2006
117) TO_DATE() with INSERT statement
118) TO_DATE(x[, format]) converts the x string to a datetime
119) TO_TIMESTAMP(x, format) convert the string x ( CHAR , VARCHAR2 , NCHAR , or NVARCHAR2 ) to a TIMESTAMP
120) TO_TIMESTAMP_TZ(2002-06-18 13
121) TO_TIMESTAMP_TZ(x,[format]) convert x to a TIMESTAMP WITH TIMEZONE with an optional format for x
122) TRUNC()
123) TRUNC() parameter list
124) TRUNC(date_in,MM)
125) TRUNC(date_in,YYYY)
126) TRUNC(x[, unit]) truncates x
127) Truncates 7
128) Truncates May 25, 2005, to the first day in the month
129) Truncating to the Quarter
130) Use the MONTHS_BETWEEN function
131) Use TRUNC(date_variable) when you want to extract a date from a timestamp
132) Using Date Arithmetic
133) Using Datetime Functions
134) Using the RR Format
135) Using the YY Format